/*
  Tên:Trần Hoàng Lâm
  MSSV:11379951
  Lớp:DHTH7DLT
  Ngày hoàn thành:28/04/2012
  */
#include "frbaocaoton.h"
#include "ui_frbaocaoton.h"


frbaocaoton::frbaocaoton(QWidget *parent) :
    QDialog(parent),
    ui(new Ui::frbaocaoton)
{

    ui->setupUi(this);
    DB=new MySqlConnection();    
    this->connect(ui->btnLoc,SIGNAL(clicked()),this,SLOT(locMaSach()));
    this->connect(ui->btnCapNhatTonKho,SIGNAL(clicked()),this,SLOT(capNhatTonKho()));
    this->connect(ui->dtpNgay,SIGNAL(dateChanged(QDate)),this,SLOT(eventSelectDateChange()));
    ui->dtpNgay->setDate(QDate::currentDate());
    ngaythang=ui->dtpNgay->date().toString("yyyyMM");
    loadDS(ngaythang);

}

frbaocaoton::~frbaocaoton()
{
    delete ui;
}

void frbaocaoton::eventSelectDateChange()

{
    ngaythang=ui->dtpNgay->date().toString("yyyyMM");
    if(ngaythang!=QDate::currentDate().toString("yyyyMM"))
    {
       QSqlQuery query=DB->exec("select ThangNam from h2l_TonKho where ThangNam='"+ngaythang+"'");
        query.first();
        if(query.size()==1)
        {
               loadPreviewTon(ngaythang);//ds tháng trước
        }
        else
        {
            if(QMessageBox::information(this,trUtf8("Thông Báo"),trUtf8("Không có báo cáo tồn trong tháng ")+ui->dtpNgay->text()))
                              return;

        }
    }
    else
        {
            loadDS(ngaythang);//ds trong tháng
        }

}

void frbaocaoton::locMaSach()
{

    QList<QStandardItem *> tim= dataTonKho->findItems(ui->txtMaSach->text(),Qt::MatchExactly,0);

    if(tim.count()==1)
    {
        QModelIndex index=tim.first()->index();
        int vt=index.row();
        ui->tbviewTon->selectRow(vt);

    }
}

int frbaocaoton::tinhSachNhapTrongThang(QString maSach, QString thang)
{
    QSqlQuery query=DB->exec("select EXTRACT(YEAR_MONTH FROM NgayLap) as Thang,ct.MaSach,sum(SoLuong) as soluong from h2l_HoaDonNhap hd, h2l_ChiTietHoaDonNhap ct where hd.MaHD=ct.MaHD group by year(ngaylap), MONTH(NgayLap),maSach having Thang='"+thang+"' and MaSach="+maSach);
    if(query.isSelect())
        if(query.first())
        return query.value(2).toInt();
    return 0;

}

void frbaocaoton::loadDS(QString NgayThang)
{


    dataTonKho=new QStandardItemModel();
    QSqlQuery query=DB->exec("select maSach,TenSach,SoLuongTon from Sach");
    query.exec();
    QStringList lstMaSach;
    QStringList lstTenSach;
    QStringList lstSoLuongTon;
    if(query.size()>0)
    while(query.next())
    {
        lstMaSach+=query.value(0).toString();
        lstTenSach+=query.value(1).toString();
        lstSoLuongTon+=query.value(2).toString();
    }

    for(int i=0;i<lstMaSach.size();i++)
    {
        int nhapsach=tinhSachNhapTrongThang(lstMaSach.value(i),NgayThang);
        int bansach=tinhSachBanTrongThang(lstMaSach.value(i),NgayThang);
        int itondau=lstSoLuongTon.at(i).toInt();
        int iphatsinh=nhapsach-bansach;
        int itongcuoi=itondau+iphatsinh;
        QStandardItem *masach = new QStandardItem(QString(lstMaSach.at(i)));
        QStandardItem *sach = new QStandardItem(QString(lstTenSach.at(i)));
        QStandardItem *tondau = new QStandardItem(QString::number(itondau));
        QStandardItem *phatsinh = new QStandardItem(QString::number(iphatsinh));
        QStandardItem *cuoi = new QStandardItem(QString::number(itongcuoi));
        dataTonKho->setItem(i, 0,masach);
        dataTonKho->setItem(i, 1,sach);
        dataTonKho->setItem(i, 2,tondau);
        dataTonKho->setItem(i, 3,phatsinh);
        dataTonKho->setItem(i, 4,cuoi);

    }
    dataTonKho->setHeaderData(0, Qt::Horizontal,trUtf8("Mã sách"));
    dataTonKho->setHeaderData(1, Qt::Horizontal,trUtf8("Sách"));
    dataTonKho->setHeaderData(2, Qt::Horizontal,trUtf8("Tồn đầu"));
    dataTonKho->setHeaderData(3, Qt::Horizontal,trUtf8("Phát sinh"));
    dataTonKho->setHeaderData(4, Qt::Horizontal,trUtf8("Cuối"));
    ui->tbviewTon->setModel(dataTonKho);
    ui->tbviewTon->setColumnHidden(0,true);
    ui->tbviewTon->setColumnWidth(1,300);
    ui->tbviewTon->setColumnWidth(2,100);
    ui->tbviewTon->setColumnWidth(3,100);
    ui->tbviewTon->setColumnWidth(4,100);
}


int frbaocaoton::tinhSachBanTrongThang(QString maSach, QString thang)
{
    QSqlQuery query=DB->exec("select EXTRACT(YEAR_MONTH FROM NgayLap) as Thang,ct.MaSach,sum(SoLuong) as soluong from h2l_HoaDonXuat hd, h2l_ChiTietHoaDonXuat ct where hd.MaHD=ct.MaHD group by year(ngaylap), MONTH(NgayLap),maSach having Thang='"+thang+"' and MaSach="+maSach);
    if(query.isSelect())
        if(query.first())
        return query.value(2).toInt();
    return 0;
}

void frbaocaoton::luuTonkho()
{
    //Tự động lưu.Giờ hệ thống phải chính xác
    //Kiểm tra tồn kho có chưa
    bool tl=false;
    //Kiểm tra tháng hiện tại
    QStringList lstNgayThang;
    QStringList lstMaSach;
    QStringList lstTonDau;
    QStringList lstPhatSinh;
    QStringList lstTonCuoi;
    //lấy tháng trước
    QString thangtruoc;
    QSqlQuery query=DB->exec("select ThangNam from h2l_TonKho order by ThangNam desc limit 0,1");
    if(query.isSelect())
    {
        query.first();
        thangtruoc=query.value(0).toString();
    }
    else
        thangtruoc=QString::null;
    query.clear();
    QString thangNamHT=QDate::currentDate().toString("yyyyMM");
    query=DB->exec("select ThangNam from h2l_TonKho where ThangNam='"+thangNamHT+"'");
    query.first();
    if(query.size()==1)//Có tồn tại =>Update
    {

        query=DB->exec("select maSach,SoLuongTon from Sach");
        query.exec();
        //clear list
        if(query.size()>0)
        {
            while(query.next())
            {
                lstMaSach+=query.value(0).toString();
                lstTonDau+=query.value(1).toString();
            }
        }
        else
            return;
        for(int i=0;i<lstMaSach.size();i++)
        {
            int nhapsach=tinhSachNhapTrongThang(lstMaSach.value(i),thangNamHT);
            int bansach=tinhSachBanTrongThang(lstMaSach.value(i),thangNamHT);
            int itondau=lstTonDau.at(i).toInt();
            int iphatsinh=nhapsach-bansach;
            int itongcuoi=itondau+iphatsinh;
            lstNgayThang+=thangtruoc;
            lstPhatSinh+=QString::number(iphatsinh);
            lstTonCuoi+=QString::number(itongcuoi);
        }

        QSqlQuery query(DB->DB);
        //update lai ngay ton kho
        query.prepare("UPDATE h2l_TonKho SET NgayLap=? WHERE ThangNam=?");
        query.bindValue(0,QDate::currentDate());
        query.bindValue(1,thangtruoc);
        query.exec();
        query.clear();
        //end update
        query.prepare("Delete from h2l_ChiTietTonkho  WHERE ThangNam=?");
        query.bindValue(0,QDate::currentDate().toString("yyyyMM"));
        query.exec();
        query.clear();
        query.prepare("INSERT INTO h2l_ChiTietTonkho (ThangNam,MaSach,TonDau,PhatSinh,TonCuoi) VALUES (?,?,?,?,?)");
        query.addBindValue(lstNgayThang);
        query.addBindValue(lstMaSach);
        query.addBindValue(lstTonDau);
        query.addBindValue(lstPhatSinh);
        query.addBindValue(lstTonCuoi);
        tl= query.execBatch();
        if(!tl)
            QMessageBox::information(this,trUtf8("Có lỗi xảy ra khi Update"),query.lastError().text());

    }
    else //insert
    {
        QSqlQuery query(DB->DB);
        if(thangtruoc==QDate::currentDate().toString("yyyyMM")||thangtruoc.isNull())//Bản tồn mới tạo
        {
            query.prepare("INSERT INTO h2l_TonKho (ThangNam,NgayLap) VALUES(?,?)");
            query.bindValue(0,QDate::currentDate().toString("yyyyMM"));
            query.bindValue(1,QDate::currentDate());
            query.exec();
            return;
        }
        query.clear();
        query=DB->exec("select maSach,SoLuongTon from Sach");
        query.exec();
        //clear list
        if(query.size()>0)
            while(query.next())
            {
                lstMaSach+=query.value(0).toString();
                lstTonDau+=query.value(1).toString();
            }
        for(int i=0;i<lstMaSach.size();i++)
        {
            int nhapsach=tinhSachNhapTrongThang(lstMaSach.value(i),thangtruoc);
            int bansach=tinhSachBanTrongThang(lstMaSach.value(i),thangtruoc);
            int itondau=lstTonDau.at(i).toInt();
            int iphatsinh=nhapsach-bansach;
            int itongcuoi=itondau+iphatsinh;
            lstNgayThang+=thangtruoc;
            lstPhatSinh+=QString::number(iphatsinh);
            lstTonCuoi+=QString::number(itongcuoi);

        }        

        //Lưu vào bảng
        //update lai ngay ton kho
        query.prepare("UPDATE h2l_TonKho SET NgayLap=? WHERE ThangNam=?");
        query.bindValue(0,QDate::currentDate());
        query.bindValue(1,thangtruoc);
        query.exec();
        query.clear();
        //end update
        query.prepare("Delete from h2l_ChiTietTonkho  WHERE ThangNam=?");
        query.bindValue(0,thangtruoc);
        query.exec();
        query.clear();
        query.prepare("INSERT INTO h2l_ChiTietTonkho (ThangNam,MaSach,TonDau,PhatSinh,TonCuoi) VALUES (?,?,?,?,?)");
        query.addBindValue(lstNgayThang);
        query.addBindValue(lstMaSach);
        query.addBindValue(lstTonDau);
        query.addBindValue(lstPhatSinh);
        query.addBindValue(lstTonCuoi);
        tl= query.execBatch();
        if(!tl)
            QMessageBox::information(this,trUtf8("Có lỗi xảy ra khi Update"),query.lastError().text());
        query.clear();
        //End lưu
        //Cập nhật lại giá trị tồn cho sách

        //
        query.prepare("INSERT INTO h2l_TonKho (ThangNam,NgayLap) VALUES(?,?)");
        query.bindValue(0,QDate::currentDate().toString("yyyyMM"));
        query.bindValue(1,QDate::currentDate());
        query.exec();
        query.clear();
        //Xử lý số liệu tháng cũ update lai ton kho
        query.prepare("Update Sach set Sach.SoLuongTon=(select TonCuoi from h2l_ChiTietTonkho where ThangNam=? and Sach.MaSach=h2l_ChiTietTonkho.MaSach)");
        query.bindValue(0,thangtruoc);
        query.exec();
        query.clear();
        //end xử lý số liệu tháng cũ
        //Insert ton kho thang moi vao
        query.prepare("INSERT INTO h2l_TonKho (ThangNam,NgayLap) VALUES(?,?)");
        query.bindValue(0,QDate::currentDate().toString("yyyyMM"));
        query.bindValue(1,QDate::currentDate());
        query.exec();

    }
    DB->Close();



}

void frbaocaoton::capNhatTonKho()
{
    if(QMessageBox::information(this,trUtf8("Thông Báo"),trUtf8("Bạn nên thực hiện chức năng này đầu mỗi tháng.Để lưu thông tin tồn kho.\nBạn có muốn tiếp tục ?"),QMessageBox::Yes,QMessageBox::No)==QMessageBox::Yes)
        luuTonkho();
}

void frbaocaoton::loadPreviewTon(QString NgayThang)
{
    QSqlQuery query=DB->exec("SELECT s.MaSach,TenSach,TonDau,PhatSinh,TonCuoi FROM QLBSach.h2l_ChiTietTonkho ct,Sach s where ct.MaSach=s.MaSach and ThangNam='"+NgayThang+"'");
    query.exec();
    QStringList lstMaSach;
    QStringList lstTenSach;
    QStringList lstTonDau;
    QStringList lstPhatSinh;
    QStringList lstTonCuoi;    ;
    if(query.size()>0)
    {
        while(query.next())
        {
            lstMaSach+=query.value(0).toString();
            lstTenSach+=query.value(1).toString();
            lstTonDau+=query.value(2).toString();
            lstPhatSinh+=query.value(3).toString();
            lstTonCuoi+=query.value(4).toString();
        }
    }
    else
        return;
    for(int i=0;i<lstMaSach.size();i++)
    {

        QStandardItem *masach = new QStandardItem(QString(lstMaSach.at(i)));
        QStandardItem *sach = new QStandardItem(QString(lstTenSach.at(i)));
        QStandardItem *tondau = new QStandardItem(QString(lstTonDau.at(i)));
        QStandardItem *phatsinh = new QStandardItem(QString(lstPhatSinh.at(i)));
        QStandardItem *cuoi = new QStandardItem(QString(lstTonCuoi.at(i)));
        dataTonKho->setItem(i, 0,masach);
        dataTonKho->setItem(i, 1,sach);
        dataTonKho->setItem(i, 2,tondau);
        dataTonKho->setItem(i, 3,phatsinh);
        dataTonKho->setItem(i, 4,cuoi);

    }
    dataTonKho->setHeaderData(0, Qt::Horizontal,trUtf8("Mã sách"));
    dataTonKho->setHeaderData(1, Qt::Horizontal,trUtf8("Sách"));
    dataTonKho->setHeaderData(2, Qt::Horizontal,trUtf8("Tồn đầu"));
    dataTonKho->setHeaderData(3, Qt::Horizontal,trUtf8("Phát sinh"));
    dataTonKho->setHeaderData(4, Qt::Horizontal,trUtf8("Cuối"));
    ui->tbviewTon->setModel(dataTonKho);
    ui->tbviewTon->setColumnHidden(0,true);
    ui->tbviewTon->setColumnHidden(0,true);
    ui->tbviewTon->setColumnWidth(1,300);
    ui->tbviewTon->setColumnWidth(2,100);
    ui->tbviewTon->setColumnWidth(3,100);
    ui->tbviewTon->setColumnWidth(4,100);



}


void frbaocaoton::on_btnThoat_clicked()
{
    this->close();
}
